﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Data;
using System.Data.OracleClient;
using System.ServiceModel.Activation;
using System.IO;
using System.IO.Compression;
using System.Data.SqlClient;
using System.ServiceModel.Configuration;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using System.ServiceModel.Channels;
using System.Runtime.CompilerServices;
using System.ServiceModel.Web;
using System.Xml.Linq; 
using System.Text.RegularExpressions;
using System.Net;
using VAdvantage.DataBase;

using VAdvantage.Logging;

namespace VAdvantageSvc
{
    [ServiceContract(Namespace = "VAdvantageSvc")]
    public interface IDataServices
    {
        [OperationContract]
        DataSetData GetDataObject(string sql, int pageNumber, int pageSize, out CustomException ServiceError);
        [OperationContract]
        String SetDataObject();

        [OperationContract]
        String GetDataXML(string sql, out CustomException ServiceError);

        [OperationContract]
        String GetDataXMLPaging(string sql, int start, int end, bool? state, string tableName);

        [OperationContract]
        XElement GetDataXMLPagingX(string sql, int start, int end, bool? state, string tableName);

        [OperationContract]
        String SetDataXML(string xml, out CustomException error);

        [OperationContract]
        String GetScalar(string xml, out CustomException serviceError);

        [OperationContract]
        Dictionary<string, string> GetAttendance(List<List<object>> attendance);//, List<List<string>> attendanceBackUp);

    }


    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Multiple)]
    public class DataServices : IDataServices
    {

        private string connectionString = System.Configuration.ConfigurationSettings.AppSettings["oracleConnectionString"];

        #region "DataObject"


        public DataSetData GetDataObject(string sql, out CustomException error)
        {
            return GetDataObject(sql, 0, 0, out  error);
        }
        public DataSetData GetDataObject(string sql, int pageNumber, int pageSize, out CustomException ServiceError)
        {
            try
            {
                DataSet ds = GetDataSet(sql, pageNumber, pageSize);
                ServiceError = null;

                DataSetData ds1 = DataSetData.FromDataSet(ds);

                return ds1;
            }
            catch (Exception ex)
            {
                ServiceError = new CustomException(ex);
            }
            return null;
        }

        private DataSet GetDataSet(string SQL, int PageNumber, int PageSize)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);
            try
            {
                //int indexOrder = SQL.IndexOf("ORDER");
                //if (indexOrder > -1)
                //{
                //    SQL = SQL.Substring(0, indexOrder) + " AND RowNum < 51 " + SQL.Substring(indexOrder); 
                //}


                Connection.Open();
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand(SQL);
                adapter.SelectCommand.Connection = Connection;
                ds = new DataSet();

                if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                {
                    adapter.Fill(ds, (PageNumber - 1) * PageSize, PageSize, "Data");
                    //long RowCount = PageNumber * PageSize;
                    //OracleCommand cmd = new OracleCommand("SET ROWCOUNT " + RowCount.ToString() + " SET NO_BROWSETABLE ON", (OracleConnection)Connection);
                }
                else
                {
                    adapter.Fill(ds, "Data");
                }

                adapter.FillSchema(ds, SchemaType.Mapped, "DataSchema");
                if (PageSize > 0) // Reset Rowcount back to 0
                {
                    //OracleCommand cmd = new OracleCommand("SET ROWCOUNT 0 SET NO_BROWSETABLE OFF", (OracleConnection)Connection);
                    //cmd.ExecuteNonQuery();
                }
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables["Data"];
                    DataTable schema = ds.Tables["DataSchema"];
                    data.Merge(schema);
                    ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                Connection.Close();
            }
            return ds;
        }

        public string SetDataObject()
        {
            throw new NotImplementedException();
        }

        #endregion  "DataObject"

        #region "XmlString"

        public string SetDataXML(string sql, out CustomException ServiceError)
        {
            ServiceError = null;
            int i = -1;
            try
            {
                i = DB.ExecuteQuery(sql);//, paramIn, trxName);
            }
            catch (Exception e)
            {
                ServiceError = new CustomException(e);
            }
            return i.ToString();
        }

        public string GetScalar(string sql, out CustomException ServiceError)
        {
            ServiceError = null;
            object i = null;
            try
            {
                i = DB.ExecuteScalar(sql);
            }
            catch (Exception e)
            {
                ServiceError = new CustomException(e);
            }
            return (i == null || i == DBNull.Value) ? null : i.ToString();
        }

        public string GetDataXML(string sql, out CustomException error)
        {
            DataSet ds = null;
            try
            {
                ds = DB.ExecuteDataset(sql);// paramIn, trxName);
                //adapter.Fill(ds);//, 'Data');// (PageNumber - 1) * PageSize, PageSize, 'Data');
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }


                ds = Util.SetUtcDateTime(ds);

                //string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
                error = null;
                //return schema + ds.GetXml() + " </TestDataSet>";

                StringWriter sw = new StringWriter();
                ds.WriteXml(sw, XmlWriteMode.WriteSchema);
                return sw.ToString();
            }
            catch (Exception err)
            {
                error = new CustomException(err);
            }
            return "";
        }

        #endregion

        //public Byte[] GetDataBytes(string sql)
        //{
        //    DataSet ds;
        //    OracleConnection Connection = new OracleConnection(connectionString);
        //    try
        //    {
        //        Connection.Open();
        //        OracleDataAdapter adapter = new OracleDataAdapter();
        //        adapter.SelectCommand = new OracleCommand(sql);
        //        adapter.SelectCommand.Connection = Connection;
        //        //if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
        //        //{
        //        //    long RowCount = PageNumber * PageSize;
        //        //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT ' + RowCount.ToString() + ' SET NO_BROWSETABLE ON', (OracleConnection)Connection);
        //        //    cmd.ExecuteNonQuery();
        //        //}
        //        ds = new DataSet();
        //        adapter.Fill(ds);//, 'Data');// (PageNumber - 1) * PageSize, PageSize, 'Data');
        //        //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
        //        //if (PageSize > 0) // Reset Rowcount back to 0
        //        //{
        //        //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
        //        //    cmd.ExecuteNonQuery();
        //        //}
        //        if (ds.Tables.Count > 1)
        //        {
        //            DataTable data = ds.Tables[0];
        //            //DataTable schema = ds.Tables['DataSchema'];
        //            // data.Merge(schema);
        //            // ds.Tables.Remove(schema);
        //        }
        //    }
        //    catch (Exception err)
        //    {
        //        throw new FaultException("Query ->" + sql + "/n" + err.Message);
        //    }
        //    finally
        //    {
        //        Connection.Close();
        //    }

        //    string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");

        //    UnicodeEncoding d = new UnicodeEncoding();




        //    return d.GetBytes(schema + ds.GetXml() + " </TestDataSet>");

        //}

        [MethodImpl(MethodImplOptions.Synchronized)]
        public string GetDataXMLPaging(string sql, int start, int end, bool? state, String tableName)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);
            try
            {
                OracleDataAdapter adapter = new OracleDataAdapter();

                adapter.SelectCommand = new OracleCommand(sql);
                Connection.Open();
                adapter.SelectCommand.Connection = Connection;
                ds = new DataSet();
                adapter.Fill(ds, start, end - start, "Table");
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                VLogger.Get().Severe(err.Message);
                return null;
                //throw new FaultException("Query ->" + sql + "/n" + err.Message);
            }
            finally
            {
                Connection.Close();
            }

            StringBuilder xml = new StringBuilder("");
            StringBuilder schema = new StringBuilder("");

            if (state == null)
            {
                //string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
                xml.Append(ds.GetXml());
                xml.Remove(0, 12);
                xml.Remove(xml.Length - 13, 13);
            }
            else if ((bool)state)
            {
                //schema.Append(ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>"));
                schema.Append("<TestDataSet>");
                xml.Append(ds.GetXml());
                xml.Remove(xml.Length - 13, 13);
                xml.Insert(0, schema.ToString());
            }
            else
            {
                xml.Append(ds.GetXml());
                xml.Remove(0, 12).Append("</TestDataSet>"); ;
            }

            return xml.ToString();
        }

        [MethodImpl(MethodImplOptions.Synchronized)]
        public XElement GetDataXMLPagingX(string sql, int start, int end, bool? state, String tableName)
        {
            DataSet ds;
            OracleConnection Connection = new OracleConnection(connectionString);

            try
            {

                OracleDataAdapter adapter = new OracleDataAdapter();

                //string sqlFrom = sql.Substring(sql.IndexOf("FROM"));

                //if (state == null)
                //{
                //    sql = "SELECT * FROM ( SELECT " + tableName + ".* , RowNum as rn " + sqlFrom + " ) WHERE rn >= " + start + " AND  rn < " + end;
                //}
                //else if ((bool)state)
                //{
                //    sql = "Select * FROM ( SELECT " + tableName + ".* , RowNum as rn " + sqlFrom + " ) WHERE rn < " + end;
                //}

                //else
                //{
                //    sql = "SELECT * FROM ( SELECT " + tableName + ".* , RowNum as rn " + sqlFrom + " )  WHERE rn >= " + start;
                //}

                adapter.SelectCommand = new OracleCommand(sql);


                Connection.Open();

                adapter.SelectCommand.Connection = Connection;
                //if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                //{
                //    long RowCount = PageNumber * PageSize;
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT ' + RowCount.ToString() + ' SET NO_BROWSETABLE ON', (OracleConnection)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                ds = new DataSet();
                adapter.Fill(ds, start, end - start - 1, "Table");
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw new FaultException("Query ->" + sql + "/n" + err.Message);
            }
            finally
            {
                Connection.Close();
            }

            // StringBuilder xml = new StringBuilder("");
            String xml = null;
            StringBuilder schema = new StringBuilder("");

            if (state == null)
            {
                //string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
                xml = ds.GetXml();
                //xml =  xml.Replace("\r", "");
                //xml =  xml.Replace("\n", "");
                //xml =  xml.Replace("\t", "");
                //xml.Remove(0, 12);
                //xml.Remove(xml.Length - 13, 13);
            }
            else if ((bool)state)
            {
                //schema.Append(ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>"));
                xml = ds.GetXml();
                //xml =  xml.Replace("\r", "");
                //xml =  xml.Replace("\n", "");
                //xml =  xml.Replace("\t", "");
                //xml.Insert(0, "<TestDataSet>");
                //xml.Remove(xml.Length - 13, 13);
                //xml.Insert(0, schema.ToString());
            }
            else
            {
                xml = ds.GetXml();
                //xml = xml.Replace("\r", "");
                //xml = xml.Replace("\n", "");
                //xml = xml.Replace("\t", "");
                //xml.Remove(0, 12).Append("</TestDataSet>"); ;
            }

            xml = Regex.Replace(xml, @"\s+", " ").Trim();
            return XElement.Parse(xml);
        }

        //Get Attendance Info from local service of client and insert into database
        [MethodImpl(MethodImplOptions.Synchronized)]
        public Dictionary<string, string> GetAttendance(List<List<object>> attendance)
        {
            int clientId = 0;
            int orgId = 0;
            int enrollId = 0;
            int machineNumber = 0;
            int verifyMode = 0;
            int inOutMode = 0;
            StringBuilder ipAddress = new StringBuilder();
            StringBuilder timestr = new StringBuilder();

            DateTime time = new DateTime();
            string strQuery = null;
            string strUpdate = "";
            string strBackup = "";
            int nextAttenednceId = 0;
            int isallInserted = 0;
            int checkUpdate = 0;
            object GetBackUpID = 0;
            StringBuilder date = new StringBuilder("");
            StringBuilder date2 = new StringBuilder("");
            StringBuilder strInsert = new StringBuilder();
            StringBuilder dateTime = new StringBuilder();
            Dictionary<string, string> returnValue = new Dictionary<string, string>();

            //Get record one by one and insert into database tables
            for (int i = 0; i < attendance.Count; i++)
            {
                timestr.Length = 0;
                ipAddress.Length = 0;
                List<object> test = attendance[i];
                clientId = Convert.ToInt32(test[0]);
                orgId = Convert.ToInt32(test[1]);
                enrollId = Convert.ToInt32(test[2]);
                machineNumber = Convert.ToInt32(test[3]);
                ipAddress.Append(test[4]);
                verifyMode = Convert.ToInt32(test[5]);
                inOutMode = Convert.ToInt32(test[6]);
                timestr.Append(test[7]);

                time = Convert.ToDateTime((timestr.ToString())).ToLocalTime();
                //Check DAta is present in database or not..
                dateTime.Length = 0;
                dateTime.Append(GlobalVariable.TO_DATE(time, false));
                strQuery = "Select HR_EmployeeAttendanceLog_ID from HR_EmployeeAttendanceLog where enroll_ID=" + enrollId + " and timeofattendance=" + GlobalVariable.TO_DATE(time, false);
                object id = DB.ExecuteScalar(strQuery);
                if (id == DBNull.Value || id == null)
                {
                    //Get Org_Id Of enroll number
                    strQuery = "Select AD_org_id from C_BPartner where enroll_ID=" + enrollId;
                    orgId = Convert.ToInt32(DB.ExecuteScalar(strQuery));
                    nextAttenednceId = DB.GetNextID(clientId, "HR_EmployeeAttendanceLog", "");
                    strInsert.Length = 0;
                    strInsert.Append("insert into HR_EmployeeAttendanceLog(Ad_Client_id,Ad_Org_ID,hr_employeeAttendanceLog_ID,enroll_ID,MachineNumber,ipAddress,verifyMode,inOutMode,timeofattendance) " +
                    "values (" + clientId + "," + orgId + "," + nextAttenednceId + "," + enrollId + "," + machineNumber + ",'" + ipAddress + "'," + verifyMode + "," + inOutMode + "," + dateTime.ToString() + ")");

                    if (DB.ExecuteQuery(strInsert.ToString()) == -1)
                    {
                        isallInserted = 0;
                        // log.Info("Unable to insert record having enroll_ID=" + enrollId + " Time Of Attendance=" + dateTime.ToString() + " from device=" + ipAddress);
                    }
                    else
                    {
                        isallInserted++;
                    }
                    // reset the date and date2
                    date.Length = 0;
                    date2.Length = 0;
                    date.Append(Convert.ToDateTime(timestr.ToString()).ToString("dd-MMM-yyyy"));
                    date2.Append(Convert.ToDateTime(Convert.ToDateTime(date.ToString()).AddDays(1).ToString()).ToString("dd-MMM-yyyy"));

                    strUpdate = "Update HR_EmployeeAttendanceLog set firstAttendance='N' where HR_EmployeeAttendanceLog_ID in(Select HR_EmployeeAttendanceLog_ID from HR_EmployeeAttendanceLog where enroll_Id=" + enrollId + " and timeofattendance>= '" + date.ToString() + "' and timeofattendance<'" + date2.ToString() + "' and FirstAttendance='Y')";
                    //log.Info(strUpdate);
                    checkUpdate = DB.ExecuteQuery(strUpdate);
                    strUpdate = "Update HR_EmployeeAttendanceLog set firstAttendance='Y' where HR_EmployeeAttendanceLog_ID in (Select HR_EmployeeAttendanceLog_ID from HR_EmployeeAttendanceLog where timeofattendance in (Select min(timeofattendance) from hr_employeeAttendanceLog where enroll_Id=" + enrollId + " and timeofattendance>= '" + date.ToString() + "' and timeofattendance<'" + date2.ToString() + "'))";
                    //log.Info(strUpdate);
                    checkUpdate = DB.ExecuteQuery(strUpdate);
                    strUpdate = "Update HR_EmployeeAttendanceLog set LAstAttendance='N' where HR_EmployeeAttendanceLog_ID in(Select HR_EmployeeAttendanceLog_ID from HR_EmployeeAttendanceLog where enroll_Id=" + enrollId + " and timeofattendance>= '" + date.ToString() + "' and timeofattendance<'" + date2.ToString() + "' and LastAttendance = 'Y')";
                    //log.Info(strUpdate);
                    checkUpdate = DB.ExecuteQuery(strUpdate);
                    strUpdate = "Update HR_EmployeeAttendanceLog set LAstAttendance='Y' where HR_EmployeeAttendanceLog_ID in(Select HR_EmployeeAttendanceLog_ID from HR_EmployeeAttendanceLog where timeofattendance in (Select max(timeofattendance) from hr_employeeattendancelog where enroll_Id=" + enrollId + " and firstAttendance = 'N' and timeofattendance>='" + date.ToString() + "' and timeofattendance<'" + date2.ToString() + "'))";
                    //log.Info(strUpdate);
                    checkUpdate = DB.ExecuteQuery(strUpdate);

                    strQuery = "Select HR_AttendanceLogBackUp_ID from HR_AttendanceLogBackUp where enroll_ID=" + enrollId + " and timeofattendance='" + timestr + "'";
                    GetBackUpID = DB.ExecuteScalar(strQuery);
                    if (GetBackUpID == DBNull.Value || GetBackUpID == null)
                    {
                        int BackUpID = DB.GetNextID(clientId, "HR_AttendanceLogBackUp", "");
                        strBackup = "insert into HR_AttendanceLogBackUp(Ad_Client_id,Ad_Org_ID,HR_AttendanceLogBackUp_ID,enroll_ID,MachineNumber,ipAddress,verifyMode,inOutMode,timeofattendance) " +
                        "values (" + clientId + "," + orgId + "," + BackUpID + "," + enrollId + "," + machineNumber + ",'" + ipAddress + "'," + verifyMode + "," + inOutMode + ",'" + test[8].ToString() + "')";
                        int check = DB.ExecuteQuery(strBackup);
                    }
                }
            }


            //if all records are inserted into database then return 'Y' otherwise 'F'
            //if (isallInserted > 0)
            //{
                returnValue["isAllInserted"] = isallInserted.ToString();
            //}
            //else
            //{
            //    returnValue["isAllInserted"] = "N";
            //}

            //return dictionary containing returnvalue 'Y' or "F' to local service
            return returnValue;
        }

    }
}
